package util;

import javax.xml.transform.Result;
import java.sql.*;

public class JDBC {
    private static String driver="com.mysql.jdbc.Driver"; //驱动名
    private static String url="jdbc:mysql://localhost:3306/course_select_system?useUnicode=true&characterEncoding=utf8"; //连接数据库的URL地址
//    private static String url="jdbc:mysql://localhost:3306/course_select_system?useUnicode=true&characterEncoding=utf8"; //连接数据库的URL地址
    private static String username="root"; //数据库的用户名
    private static String password="root"; //数据库的密码
    private static Connection con=null;
    private static PreparedStatement pst=null;
    static {
        try {
            Class.forName(driver);
        }catch (Exception ex){
            System.out.println("数据库驱动加载失败");
            ex.printStackTrace();
        }
    }
    public static void connectionTest(){
        if(con!=null){
            System.out.println("数据库已连接");
        }else{
            System.out.println("数据库未连接");
        }
    }
    public static Connection getConnection(){
        if (con==null){
            try {
                con=DriverManager.getConnection(url,username,password);
            } catch (SQLException e) {
                System.out.println("连接数据库失败,请检查用户名和密码，以及数据库是否存在");
                e.printStackTrace();
            }
        }
        return con;
    }
    public static PreparedStatement getStatement(String sql){
        getConnection();
        try {
            pst=con.prepareStatement(sql);
        } catch (SQLException e) {
            System.out.println("sql语句有误");
            e.printStackTrace();
        }
        return pst;
    }
    public static void closeConnection(){
        if(con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                System.out.println("数据库关闭失败");
                e.printStackTrace();
            }
        }
    }
    public static void closeStatement(){
        if (pst!=null){
            try {
                pst.close();
            } catch (SQLException e) {
                System.out.println("Statement关闭失败");
                e.printStackTrace();
            }
        }
    }
    public static void close(){
        closeStatement();
        closeConnection();
    }
    public static void executeUpdate(String sql){
        pst=JDBC.getStatement(sql);
        try {
            pst.executeUpdate();
            System.out.println("sql语句执行成功");
        } catch (SQLException e) {
            System.out.println("sql语句执行失败");
            e.printStackTrace();
        }
    }
    public static ResultSet executeQuery(String sql){
        ResultSet rs=null;
        pst=JDBC.getStatement(sql);
        try {
            rs=pst.executeQuery();
            System.out.println("sql查询语句执行成功");
        } catch (SQLException e) {
            System.out.println("sql查询语句执行失败");
            e.printStackTrace();
        }
        return rs;
    }
    public static void main(String[] args){
        JDBC.connectionTest();
        String sql="select count(*) as count from user where username=123 and password=123";
        ResultSet rs=JDBC.executeQuery(sql);
        try {
            while (rs.next()){
                int number=rs.getInt("count");
                System.out.println(number);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //String sql="insert into user(username,password) values(123,123)";
//        String sql="select username,password from user";
//        ResultSet rs=JDBC.executeQuery(sql);
//        try{
//            while (rs.next()){
//                System.out.println("username:"+rs.getString("username"));
//                System.out.println("password:"+rs.getString("password"));
//            }
//        }catch (Exception ex){
//            ex.printStackTrace();
//        }
    }
}